<?php
class Model_import extends Model {
    function Model_import() {
        parent::Model();
        $this->fromSession = $this->usersession->currentUser();
    }

    function bringImport($table, $s_limit, $e_limit, $sort, $dir, $data) {

        $this->db->flush_cache();
        $this->db->select(" SQL_CALC_FOUND_ROWS {$table}.* ",false) ;
        $this->db->where("create_by", $data["create_by"]);
        $this->db->order_by($sort, $dir);
        $this->db->limit($e_limit, $s_limit);
        $query = $this->db->get($table);
        $result = $query->result_array();
        //$totalCount = $this->db->count_all_results($table);
        $totalCount = $this->db->query('SELECT FOUND_ROWS() total_rows')->row()->total_rows;


        //scot companiiile
        $this->db->flush_cache();

        $this->db->select(" distinct(lower(company)) as  company ");
        $query_comp = $this->db->get("persons");
        $result_comp = $query_comp->result_array();
        foreach ($result_comp as $comp) {
            $aComp[] = $comp["company"];
        }

        foreach ($result as $key => $import) {
            /*
             0-company             
            */
            //settings ar fi tb sa tina niste valori (schimbari)
            $settings = "00";
            //echo mb_convert_case($import["company"], MB_CASE_LOWER, "UTF-8")." ";
            if (in_array(strLower($import["company"]), $aComp)) {
                $settings[0] = "1";
            }
            $result[$key]["settings"] = $settings;
        }

        $data = array(
            'totalCount' => $totalCount,
            'DS_PERSONS' => $result,
            'type' => $result[0]["type"]

        );
        return $data;
    }


    function modifyContact($data) {

        $persons_id = json_decode($data["modify_4_users"]);
        foreach ($persons_id as $person)
            $person_id[] = $person->id;

        $this->db->flush_cache();
        $this->db->where_in("person_id", $person_id);

        $dataUpdate = array();

        if ($data["corector_address"] != '')
            $dataUpdate["address"] = strTitle($data["corector_address"]);

        if ($data["corector_lname"] != '') {
            $dataUpdate["lname"] = strTitle($data["corector_lname"]);
        }

        if ($data["corector_fname"] != '') {
            $dataUpdate["fname"] = strTitle($data["corector_fname"]);
        }

        if ($data["corector_company"] != '')
            $dataUpdate["company"] = strUpper($data["corector_company"]);

        if (($data["corector_sex"] == '1') || ($data["corector_sex"] == '2') || ($data["corector_sex"] == '3'))
            $dataUpdate["sex"] = $data["corector_sex"];

        if ($data["corector_domain"] != '') {
            $dataUpdate["domain_id"] = $data["corector_domain"];
            $dataUpdate["domain_sub_id"] = $data["corector_domain_sub"];
        }
        if ($data["corector_position"] != '') {
            $dataUpdate["position_id"] = $data["corector_position"];
            $dataUpdate["position_sub_id"] = $data["corector_position_sub"];
        }
        if ($data["corector_division"] != '') {
            $dataUpdate["division_id"] = $data["corector_division"];
        }

        if ($data["corector_city"] != '')
            $dataUpdate["city_id"] = $data["corector_city"];
        if ($data["corector_county"] != '')
            $dataUpdate["county_id"] = $data["corector_county"];

        if ($data["corector_birth"] != '') {
            $dataUpdate["birth"] = $data["corector_birth"];
        }
        if ($data["corector_age"] != '-1') {
            $dataUpdate["age"] = $data["corector_age"];
        }
        if ($data["corector_income"] != '-1') {
            $dataUpdate["income"] = $data["corector_income"];
        }
        if ($data["corector_available"] != '-1') {
            $dataUpdate["available"] = $data["corector_available"];
        }
        if ($data["corector_priority"] != '-1') {
            $dataUpdate["priority"] = $data["corector_priority"];
        }   /*
        if ($data["corector_present"] != '-1') {
            $dataUpdate["send_present"] = $data["corector_present"];
        }  */

        if ($data["corector_employees"] != '-1')
            $dataUpdate["employees"] = $data["corector_employees"];

        if (count($dataUpdate) > 0)
            $query = $this->db->update("persons_temp", $dataUpdate);
    }


    function transferContact($data) {

        $aModifyClient = array();
        $customs["zona"] = $this->config->item("zonaAcoperireContact");
        $customs["rate"] = $this->config->item("rateCardContact");
        $customs["periodicitate"] = $this->config->item("periodicitateContact");
        $customs["available"] = $this->config->item("availableContact");
        $customs["priority"] = $this->config->item("priorityContact");
        $customs["sex"] = $this->config->item("sexContact");

        $this->db->flush_cache();

        if ($data["transfer"] == "transfer") {
            $this->db->where("duplicat", "0");
        }
        $this->db->where("create_by", $data["create_by"]);
        $this->db->where("type", "0");


        $aTemp = array();
        if (count(json_decode($data["transfer_contacts"])) > 0) {
            $transfer_contacts = json_decode($data["transfer_contacts"]);
            foreach ($transfer_contacts as $contact_id)
                $aTemp[] = $contact_id->id;

            $this->db->where_in("person_id", $aTemp);
        }
        $query = $this->db->get("persons_temp");
        $result = $query->result_array(); //


        foreach ($result as $key => $contact) {

            $id2del = $contact["person_id"];
            unset($contact["person_id"]);
            unset($contact["duplicat"]);
            unset($contact["type"]);


            $contact["date_created"] = date("Y-m-d H:m:s");
            $contact["modify_by"] = $contact["create_by"];
            $contact["date_modified"] = date("Y-m-d H:m:s");
            $contact["modify_by2"] = $contact["create_by"];
            $contact["date_modified2"] = date("Y-m-d H:m:s");

            $key = array_key_exists($contact["sex"], $customs["sex"]);
            if ($key === FALSE) $contact["sex"] = "3";
            $key = array_key_exists($contact["zona"], $customs["zona"]);
            if ($key === FALSE) $contact["zona"] = "0";
            $key = array_key_exists($contact["available"], $customs["available"]);
            if ($key === FALSE) $contact["available"] = "1";
            $key = array_key_exists($contact["priority"], $customs["priority"]);
            if ($key === FALSE) $contact["priority"] = "0";
            $key = array_key_exists($contact["rate"], $customs["rate"]);
            if ($key === FALSE) $contact["rate"] = "0";
            $key = array_key_exists($contact["periodicitate"], $customs["periodicitate"]);
            if ($key === FALSE) $contact["periodicitate"] = "0";


            if ((!is_numeric($contact["domain_id"])) && ($contact["domain_id"] != '')) {

                $this->db->flush_cache();
                //verific daca nu s-a inserat intre timp
                //e posibil ca altcineva sa introduca acest element si ca sa nu fac duplicat verific inca o data daca e safe sa inserez
                $this->db->where("parent_id=domain_id", null, false);
                $this->db->where("lower(domain.name)", strLower($contact["domain_id"]));
                $query = $this->db->get("domain");
                if ($query->num_rows() > 0) {
                    $result = $query->row_array();
                    $contact["domain_id"] = $result["domain_id"];
                }
                else
                {
                    $this->db->flush_cache();
                    $dataInsert = array('name' => strTitle($contact["domain_id"]), 'created_by' => $this->fromSession["user_id"]);
                    $this->db->insert("domain", $dataInsert);
                    $this->db->flush_cache();
                    $id = $this->db->insert_id();
                    $this->db->where('domain_id', $id);
                    $this->db->set('parent_id', $id);
                    $this->db->update("domain");
                    $contact["domain_id"] = $id;
                }
            }
            //daca nu exista domeniu resetez subdomeniul
            if ($contact["domain_id"] == '') $contact["domain_sub_id"] = '';

            if ((!is_numeric($contact["domain_sub_id"])) && ($contact["domain_sub_id"] != '')) {

                $this->db->flush_cache();
                //verific daca nu s-a inserat intre timp
                //e posibil ca altcineva sa introduca acest element si ca sa nu fac duplicat verific inca o data daca e safe sa inserez
                $this->db->where("parent_id!=domain_id", null, false);
                $this->db->where("domain.parent_id =", $contact["domain_id"]);
                $this->db->where("lower(domain.name)", strLower($contact["domain_sub_id"]));
                $query = $this->db->get("domain");
                if ($query->num_rows() > 0) {
                    $result = $query->row_array();
                    $contact["domain_sub_id"] = $result["domain_id"];
                }
                else
                {
                    $this->db->flush_cache();
                    $dataInsert = array('name' => strTitle($contact["domain_sub_id"]), 'created_by' => $this->fromSession["user_id"], 'parent_id' => $contact["domain_id"], "level" => "1");
                    $this->db->insert("domain", $dataInsert);
                    $this->db->flush_cache();
                    $id = $this->db->insert_id();
                    $contact["domain_sub_id"] = $id;
                }
            }

            if ((!is_numeric($contact["position_id"])) && ($contact["position_id"] != '')) {

                $this->db->flush_cache();
                //verific daca nu s-a inserat intre timp
                //e posibil ca altcineva sa introduca acest element si ca sa nu fac duplicat verific inca o data daca e safe sa inserez
                $this->db->where("parent_id=position_id", null, false);
                $this->db->where("lower(position.name)", strLower($contact["position_id"]));
                $query = $this->db->get("position");
                if ($query->num_rows() > 0) {
                    $result = $query->row_array();
                    $contact["position_id"] = $result["position_id"];
                }
                else
                {
                    $this->db->flush_cache();
                    $dataInsert = array('name' => strTitle($contact["position_id"]), 'created_by' => $this->fromSession["user_id"]);
                    $this->db->insert("position", $dataInsert);
                    $this->db->flush_cache();
                    $id = $this->db->insert_id();
                    $this->db->where('position_id', $id);
                    $this->db->set('parent_id', $id);
                    $this->db->update("position");
                    $contact["position_id"] = $id;
                }
            }
            //daca nu exista domeniu resetez subdomeniul
            if ($contact["position_id"] == '') $contact["position_sub_id"] = '';

            if ((!is_numeric($contact["position_sub_id"])) && ($contact["position_sub_id"] != '')) {

                $this->db->flush_cache();
                //verific daca nu s-a inserat intre timp
                //e posibil ca altcineva sa introduca acest element si ca sa nu fac duplicat verific inca o data daca e safe sa inserez
                $this->db->where("parent_id!=position_id", null, false);
                $this->db->where("position.parent_id =", $contact["position_id"]);
                $this->db->where("lower(position.name)", mb_convert_case($contact["position_sub_id"], MB_CASE_LOWER, "UTF-8"));
                $query = $this->db->get("position");
                if ($query->num_rows() > 0) {
                    $result = $query->row_array();
                    $contact["position_sub_id"] = $result["position_id"];
                }
                else
                {
                    $this->db->flush_cache();
                    $dataInsert = array('name' => mb_convert_case($contact["position_sub_id"], MB_CASE_TITLE, "UTF-8"), 'created_by' => $this->fromSession["user_id"], 'parent_id' => $contact["position_id"], "level" => "1");
                    $this->db->insert("position", $dataInsert);
                    $this->db->flush_cache();
                    $id = $this->db->insert_id();
                    $contact["position_sub_id"] = $id;
                }
            }


            $array_fields = array("division" => "division_id", "city" => "city_id", "county" => "county_id", "trust"=>"trust_id");
            foreach ($array_fields as $key => $value) {
                if ((!is_numeric($contact[$value])) && ($contact[$value] != '')) {

                    $this->db->flush_cache();
                    //verific daca nu s-a inserat intre timp
                    //e posibil ca altcineva sa introduca acest element si ca sa nu fac duplicat verific inca o data daca e safe sa inserez
                    $this->db->where("lower({$key}.name)", mb_convert_case($contact[$value], MB_CASE_LOWER, "UTF-8"));
                    $query = $this->db->get($key);
                    if ($query->num_rows() > 0) {
                        $result = $query->row_array();
                        $contact[$value] = $result[$value];
                    }
                    else
                    {
                        $this->db->flush_cache();
                        $dataInsert = array('name' => mb_convert_case($contact[$value], MB_CASE_TITLE, "UTF-8"), 'created_by' => $this->fromSession["user_id"]);
                        $this->db->insert($key, $dataInsert);
                        $id = $this->db->insert_id();
                        $contact[$value] = $id;
                    }
                }
            }

            //mai verific inca o data daca este duplicat
            //caut sa vad daca e dublura
            /*$this->db->flush_cache();
            $this->db->where("lower(lname)", strLower($contact["lname"]));
            $this->db->where("lower(fname)", strLower($contact["fname"]));
            $this->db->where("lower(address)", strLower($contact["address"]));
            $query = $this->db->get("persons");

            if ($query->num_rows() >= 1) {
                $result = $query->row_array();

                if ($this->fromSession["level"] != "supervisor") {

                $dayRule = date("Y-m-d", strtotime(date("Y-m-d", strtotime($result["date_modified2"])) . "+30 days"));

                    if (date("Y-m-d") > $dayRule) {
                        $contact["modify_by2"] = $result["modify_by"];
                        $contact["date_modified2"] = $result["date_modified"];
                        $contact["modify_by"] = $data["create_by"];
                        $contact["date_modified"] = date("Y-m-d H:i:s");

                        unset($contact["date_created"]);
                        unset($contact["create_by"]);
                    }
                    else {
                        $contact["modify_by"] = $data["create_by"];
                        $contact["date_modified"] = date("Y-m-d H:i:s");
                        unset($contact["date_created"]);
                        unset($contact["create_by"]);
                        unset($contact["modify_by2"]);
                        unset($contact["date_modified2"]);
                    }
                }
                else {
                    unset($contact["date_created"]);
                    unset($contact["create_by"]);
                    unset($contact["modify_by2"]);
                    unset($contact["date_modified2"]);
                }

                $this->db->flush_cache();
                $this->db->where("person_id", $result["person_id"]);
                $this->db->update("persons", $contact);

                $aModifyClient["contacts"][] = $result["person_id"];

            }
            else*/ {
                $this->db->flush_cache();
                $this->db->insert("persons", $contact);
                $aModifyClient["contacts"][] = $this->db->insert_id();
            }

            $this->db->flush_cache();
            $this->db->where("person_id", $id2del);
            $this->db->delete("persons_temp");
        }

        $aModifyClient["client_id"] = $contact["client_id"];
        $aModifyClient["op"] = 1;

        return $aModifyClient;
    }

    /**
     * Functie de cautare dubluri.. se scot toate contactale importate in numele unui user. Pt fiecare contact se cauta in tabela PERSONS dublura
     *
     **/
    function verifyContact($data) {
        //extragere date din contacte temporare
        $this->db->flush_cache();
        $this->db->where("create_by", $data["create_by"]);
        $this->db->where("type", "0");
        $query = $this->db->get("persons_temp");
        $result = $query->result_array();
        if ($query->num_rows() > 0) {
            foreach ($result as $contact)
            {
                //caut sa vad daca e dublura
                $this->db->flush_cache();
                $this->db->where("lower(lname)", strLower($contact["lname"]));
                $this->db->where("lower(fname)", strLower($contact["fname"]));
                $this->db->where("lower(address)", strLower($contact["address"]));
                $query = $this->db->get("persons");
                $dataPerson["duplicat"] = 0;
                if ($query->num_rows() >= 1) {
                    $result = $query->row();
                    $dataPerson["duplicat"] = $result->person_id;
                }
                $this->db->flush_cache();
                $this->db->where("person_id", $contact["person_id"]);
                $this->db->update("persons_temp", $dataPerson);
            }
        }
    }


    /**
     * Functie care imi transfera contactele externe pe campanie
     */
    function  transferExternContact($data) {

        $customs["sex"] = $this->config->item("sexContact");

        $this->db->flush_cache();
        $this->db->select("lname,fname,sex,email,email2,client_id,person_id,company");
        if (count(json_decode($data["transfer_contacts"])) > 0) {
            $transfer_contacts = json_decode($data["transfer_contacts"]);
            foreach ($transfer_contacts as $contact_id)
                $aTemp[] = $contact_id->id;

            $this->db->where_in("person_id", $aTemp);
        }
        $this->db->where("type", "1");
        $this->db->where("create_by", $data["create_by"]);
        $aResult = $this->db->get("persons_temp")->result_array();

        foreach ($aResult as $contact) {

            $key = array_key_exists($contact["sex"], $customs["sex"]);
            if ($key === FALSE) $contact["sex"] = "3";


            $aContact2Insert = array(
                "lname" => strTitle($contact["lname"]),
                "client_id" => $contact["client_id"],
                "company" => strUpper($contact["company"]),
                "fname" => strTitle($contact["fname"]),
                "email" => strLower($contact["email"]),
                "email2" => strLower($contact["email2"]),
                "sex" => $contact["sex"],
                "extern" => 1
            );



            //verificare in baza de date
            $this->db->flush_cache();
            $this->db->where("persons.lname",strTitle($contact["lname"]));
            $this->db->where("persons.company",strUpper($contact["company"]));
            $this->db->where("persons.fname",strUpper($contact["fname"]));
            $this->db->join("client_persons","client_persons.person_id=persons.person_id and client_persons.client_id= {$contact["client_id"]}","inner ");
            $queryBaza = $this->db->get("persons");

            //verificare in contacte externe
            $this->db->flush_cache();
            $queryExtern = $this->db->get_where("client_persons", $aContact2Insert);

            if (($queryBaza->num_rows() == 0) && ($queryExtern->num_rows()==0)) {
                $this->db->flush_cache();
                $this->db->insert("client_persons", $aContact2Insert);
            }

            $this->db->flush_cache();
            $this->db->where("person_id", $contact["person_id"]);
            $this->db->delete("persons_temp");
        }
    }


    function deleteContact($data) {
        $persons_id = json_decode($data["ids"]);
        foreach ($persons_id as $person)
            $person_id[] = $person->id;

        $this->db->flush_cache();
        $this->db->where_in("person_id", $person_id);
        $this->db->delete("persons_temp");
    }

    //functie care sterge datele de import de pe un user
    function delete_import($user_id) {
        $this->db->flush_cache();
        $this->db->where("create_by", $user_id);
        $this->db->delete("persons_temp");
    }

    function insert_import($data) {
        $this->db->flush_cache();
        $this->db->insert("persons_temp", $data);
    }
}//end model